package org.morningangel.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.naming.NamingException;

import org.morningangel.bean.Activity;
import org.morningangel.bean.MainPush;
import org.morningangel.dao.MainPushDao;
import org.morningangel.util.DBHelperUtil;

public class MainPushDaoImpl implements MainPushDao {

	@Override
	public Integer save(MainPush mainPush) {
		Connection conn = null;
		PreparedStatement ps = null;
		Integer suNum = 0;
		try {
			conn = DBHelperUtil.getConnection();
			//打开事务
			conn.setAutoCommit(false);
			String sql = "insert into tb_mainPush (activityId, type, start) values (?, ?, ?)";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, mainPush.getActivityId());
			ps.setInt(2, mainPush.getType());
			ps.setTimestamp(3, new Timestamp(mainPush.getStart().getTime()));
			suNum = ps.executeUpdate();
			//提交事务
			conn.commit();
			//关闭事务
			conn.setAutoCommit(true);
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(ps, conn);
		
		return suNum;
	}

	@Override
	public Integer count(Integer type) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Integer count = 0;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "selcet count(*) from tb_mainPush where type = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, type);
			rs = ps.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(rs, ps, conn);
		
		return count;
	}

	@Override
	public Integer update(MainPush mainPush) {
		Connection conn = null;
		PreparedStatement ps = null;
		Integer suNum = 0;
		try {
			conn = DBHelperUtil.getConnection();
			//打开事务
			conn.setAutoCommit(false);
			String sql = "update tb_mainPush set type = ?, end = ? where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, mainPush.getType());
			ps.setTimestamp(2, new Timestamp(mainPush.getEnd().getTime()));
			ps.setInt(3, mainPush.getId());
			suNum = ps.executeUpdate();
			//提交事务
			conn.commit();
			//关闭事务
			conn.setAutoCommit(true);
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(ps, conn);
		
		return suNum;
	}

	@Override
	public List<Activity> getActivitys(Integer type) {
		Connection conn = null;
		PreparedStatement ps = null;
		List<Activity> list = new ArrayList<Activity>();
		ResultSet rs = null;
		try {
			conn = DBHelperUtil.getConnection();
			String sql = "select a.id, a.title, a.mainImage, a.image, a.startTime, a.endTime, a.type, a.address, a.partipateNum, a.interestNum, a.sponsor, a.description, a.url, a.status from tb_activity a, tb_mainPush b where b.type = ? and a.id = b.activityId order by rand() limit 8";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, type);
			rs = ps.executeQuery();
			while(rs.next()) {
				Activity activity = new Activity();
				setActivity(rs, activity);				
				list.add(activity);
			}
		} catch (NamingException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		DBHelperUtil.close(rs, ps, conn);
		
		return list;
	}
	
	private void setActivity(ResultSet rs, Activity activity) throws SQLException {
		activity.setId(rs.getInt(1));
		activity.setTitle(rs.getString(2));
		activity.setMainImage(rs.getString(3));
		activity.setImage(rs.getString(4));
		activity.setStartTime(new Date(rs.getTimestamp(5).getTime()));
		activity.setEndTime(new Date(rs.getTimestamp(6).getTime()));
		activity.setType(rs.getString(7));
		activity.setAddress(rs.getString(8));
		activity.setPartipateNum(rs.getInt(9));
		activity.setInterestNum(rs.getInt(10));
		activity.setSponsor(rs.getString(11));
		activity.setDescription(rs.getString(12));
		activity.setUrl(rs.getString(13));
		activity.setStatus(rs.getInt(14));
	}

}
